use master
go 

create database Student 
on
(
name ='Student',
filename='D:\Student.mdf',
size=5MB,
maxsize=50MB,
filegrowth=50%
)

log on
(
name='Student_log',
filename='D:\Student_log.ldf',
size=5MB,
maxsize=50MB,
filegrowth=50%
)
go

use Student
go

create table StuInfo
(
	StuID int primary key identity(1,1),
	StuName nvarchar(10),
	StuAge int ,
	StuSex varchar(2) check (StuSex in (1,0) ),
	Time datetime ,

)

create table CourseInfo
(
	CourseID int primary key identity(1,1),
	CourseName nvarchar(20) ,
	CourseMarks int ,

)

create table ScoreInfo
(
	ScoreID int identity,
	StuID int references StuInfo(StuID),
	CourseID int references CourseInfo(CourseID),
	Score int ,
)

insert into StuInfo(StuName,StuAge,StuSex,Time) values
('Tom',19,'1',null),
('Jack',20,'0',null),
('Rose',21,'1',null),
('Lulu',19,'1',null),
('Lili',21,'0',null),
('abc',20,'1','2007-01-07 01:11:36.590')

insert into CourseInfo(CourseName,CourseMarks) values 
('JavaBase',4),('HTML',2),('JAVAScript',2)

insert into ScoreInfo(StuID,CourseID,Score) values 
(1,1,80),(1,2,85),(1,4,50),(2,1,75),(2,3,45),(2,4,75),(3,1,45),
(4,1,95),(4,2,75),(4,3,90),(4,4,45)


Select * from StuInfo
Select * from CourseInfo
Select * from ScoreInfo
--1.查询出每个学生所选修的课程的数量和所选修的课程的考试的平均分

Select T.StuID,T.StuName,count(CourseName) 选课数量, avg(Score) 平均分 from ScoreInfo S inner join StuInfo T on S.StuID=T.StuID
inner join CourseInfo C on C.CourseID=S.CourseID group by T.StuID,T.StuName

--2.查询出每门课程的选修的学生的个数和学生考试的总分
Select C.CourseName,C.CourseID,count(*) 选修的个数,sum(Score) 考试总分 from ScoreInfo S inner join   CourseInfo C on C.CourseID=S.CourseID
group by C.CourseName,C.CourseID

--3.查询出性别一样并且年龄一样的学生的信息
Select S.StuID,S.StuName,S.StuAge,S.StuSex from StuInfo S inner join
 StuInfo T on S.StuAge=T.StuAge and S.StuSex=T.StuSex and S.StuName!=T.StuName

--4.查询出学分一样的课程信息
Select * from CourseInfo C,CourseInfo R where C.CourseMarks=R.CourseMarks and C.CourseID<>R.CourseID
group by C.CourseName,C.CourseID,C.CourseMarks

--5.查询出参加了考试的学生的学号，姓名，课程号和分数 
Select S.StuID 学生学号 , StuName 姓名 , C.CourseID 课程号 , Score 分数 from ScoreInfo S inner join StuInfo I on S.StuID=I.StuID inner join
CourseInfo C on S.CourseID=C.CourseID group by S.StuID,StuName,C.CourseID,Score
--6.查询出参加了考试的学生的学号，课程号，课程名，课程学分和分数
select S.StuID 学生学号  , C.CourseID 课程号 , CourseName 课程名, CourseMarks 课程学分 , Score 分数 from ScoreInfo S inner join StuInfo I on S.StuID=I.StuID inner join 
CourseInfo C on S.CourseID=C.CourseID group by S.StuID,C.CourseID,CourseName,CourseMarks,Score
--7.查询出没有参加考试的学生的学号和姓名
select S.StuID 学号 , StuName 姓名 from ScoreInfo I right join StuInfo S on I.StuID=S.StuID where Score is null or Score=''
--8.查询出是周六周天来报到的学生
select * from StuInfo where datename(weekday,StuTime)='星期六' or datename(weekday,StuTime)='星期日'
--9.查询出姓名中有字母a的学生的信息
select * from StuInfo where StuName like '%a%' or StuName like '%A%'
--10.查询出选修了2门课程以上的并且考试平均分在70以上的学生的学号和考试平均分以及选修课程的数量
select S.StuID 学号 , COUNT(*) 课程数量 , AVG(Score) 平均分 from ScoreInfo I inner join 
StuInfo S on I.StuID=S.StuID group by S.StuID having COUNT(*)>2 and AVG(Score)>70